if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FindProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FindProduct]
GO
CREATE PROC sp_FindProduct
	@cateID	varchar(10),
	@name	nvarchar(50)
AS
BEGIN
	DECLARE @sql		nvarchar(4000)
	DECLARE @cateCon	nvarchar(1000)
	DECLARE @nameCon	nvarchar(1000)
	DECLARE @and		nvarchar(10)
	DECLARE @where		nvarchar(10)

	SET @cateCon = ''
	SET @nameCon = ''
	SET @and = ''
	SET @where = ''

	IF(@cateID <> '')
		BEGIN
			SET @cateCon = ' A.CategoryID like ''%' + @cateID + '%'''
			SET @where = 'WHERE'
		END
	IF(@name <> '')
		BEGIN
			SET @nameCon = ' A.ProductName like ''%'+@name+'%'''
			SET @where = ' WHERE'
		END
	IF(@name <> '' and @cateID <> '')
		BEGIN
			SET @and = ' AND'
		END
	
	SELECT @sql = 'SELECT A.ID, B.CategoryName, A.ProductName, A.Image, A.Description, A.Price 
		FROM Product As A INNER JOIN Categories As B ON A.CategoryID = B.ID ' + @where + @cateCon + @and + @nameCon
	EXEC sp_executesql @sql 
	--print @sql
END